﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SBLib.Common.DbKS
{
    public class DatabaseFuncKS
    {
        private string _connectionstring { get; set; }

        public DatabaseFuncKS(string ConnectionString)
        {
            this._connectionstring = ConnectionString;
        }
        public DataTable GetDataFromDB(string prmSqlStatement, string prmChangeDatabase, params object[] parameters)
        {

            DataSet retDataSet = new DataSet();
            try
            {


                if (string.IsNullOrEmpty(this._connectionstring))
                {
                    return null;
                }

                using (SqlConnection connection = new SqlConnection(this._connectionstring))
                {
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        try
                        {

                            connection.Open();
                            command.CommandText = prmSqlStatement;
                            command.CommandType = CommandType.Text;
                            command.Parameters.Clear();

                            //prepare parameters
                            int ctr = 0;
                            if (parameters != null)
                            {
                                foreach (var singleParam in parameters)
                                {
                                    command.Parameters.AddWithValue(string.Format("@{0}", ctr), singleParam);
                                    ctr = ctr + 1;
                                }
                            }

                            try
                            {
                                //if needs to change database
                                if (!string.IsNullOrEmpty(prmChangeDatabase))
                                {
                                    if (prmChangeDatabase.Trim().Length > 0)
                                    {
                                        connection.ChangeDatabase(prmChangeDatabase);
                                    }
                                }

                            }
                            catch (Exception)
                            {
                            }

                            //fill dataset 
                            try
                            {
                                using (SqlDataAdapter retAdapter = new SqlDataAdapter(command))
                                {
                                    retDataSet = new DataSet();

                                    retAdapter.Fill(retDataSet);
                                }

                                if (retDataSet != null)
                                {
                                    if (retDataSet.Tables.Count > 0)
                                    {
                                        return retDataSet.Tables[0];
                                    }

                                }

                            }
                            catch (Exception)
                            {

                            }

                            return null;
                        }
                        catch (Exception)
                        {

                            return null;
                        }
                        finally
                        {
                            if (retDataSet != null)
                            {
                                retDataSet.Dispose();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public bool ExecuteSqlStatementNonQuery(string prmSqlStatement, CommandType prmType)
        {


            try
            {


                if (string.IsNullOrEmpty(this._connectionstring))
                {
                    throw new Exception("No connectionstring");
                }

                using (SqlConnection connection = new SqlConnection(this._connectionstring))
                {
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        try
                        {

                            connection.Open();
                            command.CommandText = prmSqlStatement;
                            command.CommandType = prmType;

                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {

                                throw new Exception(ex.Message);
                            }
                            return false;
                        }
                        catch (Exception ex)
                        {

                            throw new Exception(ex.Message);
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        public bool CreateDatabaseTable(string prmDataBaseName, string prmTableName, string prmPrimary, string prmForeign, List<string> parameters)
        {
            try
            {
                StringBuilder stb = new StringBuilder();
                stb.AppendLine(string.Format("USE {0};", prmDataBaseName));


                stb.AppendLine(string.Format(" CREATE TABLE [dbo].[{0}](", prmTableName));
                stb.AppendLine(string.Format(" [{0}] [bigint] IDENTITY(1,1) NOT NULL,", prmPrimary));
                stb.AppendLine(string.Format(" [CampaignID] [int] NULL,", prmForeign));


                if (parameters != null)
                {
                    foreach (var singleParam in parameters)
                    {
                        stb.AppendLine(string.Format(" [{0}] [varchar](100) NULL,", singleParam));
                    }
                }
                stb.AppendLine(string.Format(" CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED", prmTableName));
                stb.AppendLine(" (");
                stb.AppendLine(string.Format(" [{0}] ASC", prmPrimary));
                stb.AppendLine(" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]");
                stb.AppendLine(" ) ON [PRIMARY];");




                ExecuteSqlStatementNonQuery(stb.ToString(), CommandType.Text);
                return true;
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }


        public void BulkInsert(string prmSourceConnectionString, string prmDestinationConnectionString,string prmDestinationTableName, string prmSelectStatement,List<string> prmColumns)
        {
            
            // Open a sourceConnection to the AdventureWorks database.
            using (SqlConnection sourceConnection =
                       new SqlConnection(prmSourceConnectionString))
            {
                sourceConnection.Open();


                // Get data from the source table as a SqlDataReader.
                SqlCommand commandSourceData = new SqlCommand(prmSelectStatement  , sourceConnection);
                SqlDataReader reader =
                    commandSourceData.ExecuteReader();

                // Open the destination connection. In the real world you would 
                // not use SqlBulkCopy to move data from one table to the other 
                // in the same database. This is for demonstration purposes only.
                using (SqlConnection destinationConnection =
                           new SqlConnection(prmDestinationConnectionString))
                {
                    destinationConnection.Open();

                    // Set up the bulk copy object. 
                    // Note that the column positions in the source
                    // data reader match the column positions in 
                    // the destination table so there is no need to
                    // map columns.
                    using (SqlBulkCopy bulkCopy =
                               new SqlBulkCopy(destinationConnection))
                    {
                        bulkCopy.DestinationTableName = string.Format("dbo.{0}", prmDestinationTableName);


                        foreach (string column in prmColumns)
                        {
                            bulkCopy.ColumnMappings.Add(column, column);
                        } 


                        try
                        {
                            // Write from the source to the destination.
                            bulkCopy.WriteToServer(reader);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                        finally
                        {
                            // Close the SqlDataReader. The SqlBulkCopy
                            // object is automatically closed at the end
                            // of the using block.
                            reader.Close();
                        }
                    }

                    //// Perform a final count on the destination 
                    //// table to see how many rows were added.
                    //long countEnd = System.Convert.ToInt32(
                    //    commandRowCount.ExecuteScalar());

                }

            }
        }
        public void BulkInsert(DataTable dt, string prmTableName)
        {
            try
            {

                // connect to SQL
                using (SqlConnection connection =
                        new SqlConnection(this._connectionstring))
                {
                    connection.Open();
                    SqlBulkCopy bulkCopy =
                        new SqlBulkCopy(this._connectionstring);

                    // set the destination table name
                    bulkCopy.DestinationTableName = prmTableName;
                   //bulkCopy.SqlRowsCopied+=

                    // write the data in the "dataTable"
                    bulkCopy.WriteToServer(dt);
                }


            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }
    }
}
